package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.cons.MsgTemplate;
import cn.ztydata.teachingsystem.heaven.entity.Course;
import cn.ztydata.teachingsystem.heaven.entity.CourseStatistics;
import cn.ztydata.teachingsystem.heaven.entity.Department;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.*;

/**
 * 课程Dao类
 * Created by chenxin on 2014/12/1.
 */
@Repository
public class CourseDao extends BaseDao {

    /**
     * 获取系部课程的统计数量
     * @return List
     *
     * @author cx
     * @since 2014-12-01
     */
    public List<Map<String, Object>> getTotalForDept() {
        String sql = "SELECT iDeptId AS deptId,COUNT(id) AS total FROM t_course GROUP BY iDeptId";

        log.debug("获取系部课程的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource());
    }

    /**
     * 获取系部课程总数前n名的统计
     *
     * @author chenxin
     * @since 2015/1/10
     */
    public List<Department> getTopTotalForDept(final int limit) {
        String sql = "SELECT iDeptId,COUNT(id) AS total FROM t_course GROUP BY iDeptId ORDER BY total DESC LIMIT :limit";

        log.debug("获取系部课程总数前n名的统计");
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("limit", limit), new RowMapper<Department>() {
            @Override
            public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
                Department entity = new Department();

                entity.setId(rs.getLong("iDeptId"));
                entity.setCourseTotal(rs.getLong("total"));

                return entity;
            }
        });
    }

    /**
     * 获取每个专业下的课程统计数量
     * @param deptId 系部ID
     * @return List
     *
     * @author cx
     * @since 2014-12-02
     */
    public List<Map<String, Object>> getTotalForSpec(final long deptId) {
        String sql = "SELECT iSpecialtyId AS specId,COUNT(id) AS total FROM t_course WHERE iDeptId=:deptId GROUP BY iSpecialtyId";

        log.debug("获取每个专业下的课程统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("deptId", deptId));
    }

    /**
     * 获取指定教师建课的数量
     * @param teacherIdList 教师ID
     * @return List
     *
     * @author cx
     * @since 2014-12-03
     */
    public List<Map<String, Object>> getTotalForTeacher(final List<Long> teacherIdList) {
        if (teacherIdList == null || teacherIdList.isEmpty()) {
            log.debug("教师ID集合为空:teacherIdList == null || teacherIdList.isEmpty()");
            return new ArrayList<>();
        }

        String sql = "SELECT iCreatorId AS teacherId,COUNT(id) AS total FROM t_course WHERE iCreatorId IN (:idList) GROUP BY iCreatorId";

        log.debug("获取指定教师建课的数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("idList", teacherIdList));
    }

    /**
     * 获取指定专业下的课程信息
     * @param specId 专业ID
     * @return List
     *
     * @author cx
     * @since 204-12-02
     */
    public List<CourseStatistics> getListForSpec(final long specId) {
        String sql = "SELECT id,sName FROM t_course WHERE iSpecialtyId=:specId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("specId",specId);

        log.debug("获取指定专业下的课程信息");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<CourseStatistics>() {
            @Override
            public CourseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseStatistics entity = new CourseStatistics();

                entity.setId(resultSet.getLong("id"));
                entity.setName(resultSet.getString("sName"));

                return entity;
            }
        });
    }

    /**
     * 验证指定专业下是否有课程
     * @param specialtyId 专业编号
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-12
     */
    public Boolean checkBySpecialtyId(long specialtyId){
        String sql = "SELECT COUNT(id) FROM t_course WHERE iSpecialtyId=:specialtyId LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("specialtyId", specialtyId);

        log.debug("check course count by specialtyId");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) >= 1;
    }

    /**
     * 查询教师课程
     * @param pageNum 页码
     * @param pageSize 每页条数
     * @param kind 课程种类
     * @param keywords 搜索关键字
     * @param courseIdList 课程ID集合
     * @return Page
     *
     * @author cx
     * @since 2014-12-11
     */
    public Page<Course> getList(final int pageNum, final int pageSize, final int kind, String[] keywords, List<Long> courseIdList) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,sEnName,tiKind,dtCreateTime,sCover,tiStatus FROM t_course");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_course");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //构建where条件
        StringBuilder where = new StringBuilder(" WHERE id IN (:courseIdList)");
        sps.addValue("courseIdList", courseIdList);

        //判断课程种类
        if (kind != 0) {
            log.debug("kind != 0");
            where.append(" AND tiKind=:kind");
            sps.addValue("kind", kind);
        }

        //追加搜索关键字
        if (keywords.length > 0) {
            log.debug("keywords.length > 0");
            where.append(" AND (");
            log.debug("循环搜索关键字集合");
            for (int i = 0; i < keywords.length; i++) {
                where.append("UPPER(sName) LIKE :pattern" + i + " OR ");

                sps.addValue("pattern" + i, "%" + keywords[i].toUpperCase() + "%");
            }

            //去除where语句多余字符
            where.delete(where.length() - 4, where.length());
            where.append(")");
        }

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql.append(where).append(getLimitStr(pageNum, pageSize));

        List<Course> courseList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
                Course entity = new Course();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setEnName(rs.getString("sEnName"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setCreateTime(rs.getTimestamp("dtCreateTime"));
                entity.setCover(rs.getString("sCover"));
                entity.setStatus(rs.getInt("tiStatus"));

                return entity;
            }
        });

        log.debug("查询教师课程");
        return new Page<>(total, pageSize, pageNum, courseList);
    }

    /**
     * 获取系部n个课程
     *
     * @author chenxin
     * @since 2015/1/10
     */
    public List<Course> getTopListForDept(final long deptId, final int limit) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,sEnName,tiKind,dtCreateTime,sCover FROM t_course")
                .append(" WHERE iDeptId=:deptId LIMIT :limit");
        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptId", deptId)
                .addValue("limit", limit);

        log.debug("获取系部n个课程");
        return namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
                Course entity = new Course();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setEnName(rs.getString("sEnName"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setCreateTime(rs.getTimestamp("dtCreateTime"));
                entity.setCover(rs.getString("sCover"));

                return entity;
            }
        });
    }

    /**
     * 获取专业下的n个课程信息
     *
     * @author chenxin
     * @since 2015/1/11
     */
    public List<CourseStatistics> getTopListForSpec(final long specId, final int limit) {
        String sql = "SELECT id,sName FROM t_course WHERE iSpecialtyId=:specId LIMIT :limit";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("specId", specId)
                .addValue("limit", limit);

        log.debug("获取专业下的n个课程信息");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<CourseStatistics>() {
            @Override
            public CourseStatistics mapRow(ResultSet rs, int rowNum) throws SQLException {
                CourseStatistics entity = new CourseStatistics();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));

                return entity;
            }
        });
    }

    /**
     * 新增课程
     * @param course 课程实体类
     * @return boolean
     *
     * @author cx
     * @since 2014-12-11
     */
    public boolean save(Course course) {
        String sql = "INSERT INTO t_course VALUES(NULL,:name,:enName,:deptId,:deptName,:specId,:specName,:type,:nature,:kind,:isOpen,:isCore," +
                ":credit,:theoryPeriod,:practicePeriod,:creatorId,:creatorName,Now(),0,:firstLevel,:secondLevel,NUll,:remark)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        SqlParameterSource sps = new MapSqlParameterSource().addValue("name", course.getName())
                .addValue("enName", course.getEnName())
                .addValue("deptId", course.getDeptId())
                .addValue("deptName", course.getDeptName())
                .addValue("specId", course.getSpecId())
                .addValue("specName", course.getSpecName())
                .addValue("type", course.getType())
                .addValue("nature", course.getNature())
                .addValue("kind", course.getKind())
                .addValue("isOpen", course.getIsOpen())
                .addValue("isCore", course.getIsCore())
                .addValue("credit", course.getCredit())
                .addValue("theoryPeriod", course.getTheoryPeriod())
                .addValue("practicePeriod", course.getPracticePeriod())
                .addValue("creatorId", course.getCreatorId())
                .addValue("creatorName", course.getCreatorName())
                .addValue("firstLevel", course.getFirstLevel())
                .addValue("secondLevel", course.getSecondLevel())
                .addValue("remark", course.getRemark());

        int result = namedParameterJdbcTemplate.update(sql, sps, keyHolder);
        if (result == 1) {
            log.debug("result == 1");
            course.setId(keyHolder.getKey().longValue());
            return true;
        }

        log.debug("result != 1");
        return false;
    }

    /**
     * 修改课程
     * @param course 课程实体类
     * @return boolean
     *
     * @author cx
     * @since 2014-12-12
     */
    public boolean update(Course course) {
        String sql = "UPDATE t_course SET sName=:name,sEnName=:enName,iDeptId=:deptId,sDeptName=:deptName,iSpecialtyId=:specId," +
                "sSpecName=:specName,tiType=:type,tiNature=:nature,tiKind=:kind,tiIsOpen=:isOpen,tiIsCore=:isCore,dlCredit=:credit," +
                "iTheoryPeriod=:theoryPeriod,iPracticePeriod=:practicePeriod,sRemark=:remark WHERE id=:id";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("name", course.getName())
                .addValue("enName", course.getEnName())
                .addValue("deptId", course.getDeptId())
                .addValue("deptName", course.getDeptName())
                .addValue("specId", course.getSpecId())
                .addValue("specName", course.getSpecName())
                .addValue("type", course.getType())
                .addValue("nature", course.getNature())
                .addValue("kind", course.getKind())
                .addValue("isOpen", course.getIsOpen())
                .addValue("isCore", course.getIsCore())
                .addValue("credit", course.getCredit())
                .addValue("theoryPeriod", course.getTheoryPeriod())
                .addValue("practicePeriod", course.getPracticePeriod())
                .addValue("remark", course.getRemark())
                .addValue("id", course.getId());

        log.debug("持久化课程修改");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 查询课程
     * @param courseId 课程ID
     * @return Course
     *
     * @author cx
     * @since 2014-12-12
     */
    public Course getById(final long courseId) {
        String sql = "SELECT sName,sEnName,iDeptId,sDeptName,iSpecialtyId,sSpecName,tiType,tiNature,tiKind,tiIsOpen,tiIsCore,dlCredit," +
                "iTheoryPeriod,iPracticePeriod,dtCreateTime,sCover,tiStatus,sRemark FROM t_course WHERE id=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId);

        try {
            log.debug("查询课程明细信息");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<Course>() {
                @Override
                public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Course entity = new Course();

                    entity.setId(courseId);
                    entity.setName(rs.getString("sName"));
                    entity.setEnName(rs.getString("sEnName"));
                    entity.setDeptId(rs.getLong("iDeptId"));
                    entity.setDeptName(rs.getString("sDeptName"));
                    entity.setSpecId(rs.getLong("iSpecialtyId"));
                    entity.setSpecName(rs.getString("sSpecName"));
                    entity.setType(rs.getInt("tiType"));
                    entity.setNature(rs.getInt("tiNature"));
                    entity.setKind(rs.getInt("tiKind"));
                    entity.setIsOpen(rs.getInt("tiIsOpen"));
                    entity.setIsCore(rs.getInt("tiIsCore"));
                    entity.setCredit(rs.getDouble("dlCredit"));
                    entity.setTheoryPeriod(rs.getInt("iTheoryPeriod"));
                    entity.setPracticePeriod(rs.getInt("iPracticePeriod"));
                    entity.setCreateTime(rs.getTimestamp("dtCreateTime"));
                    entity.setCover(rs.getString("sCover"));
                    entity.setRemark(rs.getString("sRemark"));
                    entity.setStatus(rs.getInt("tiStatus"));

                    return entity;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("未找到课程明细");
            log.warn(MessageFormat.format(MsgTemplate.RECORD_NOT_FOUND, "courseId", courseId));
            return null;
        }
    }

    /**
     * 查询课程封面路径
     * @param courseId 课程ID
     * @return String
     *
     * @author cx
     * @since 2014-12-13
     */
    public String getCover(final long courseId) {
        String sql = "SELECT sCover FROM t_course WHERE id=:courseId";

        try {
            log.debug("查询课程封面路径");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("courseId", courseId), String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("未找到指定课程封面");
            log.warn(MessageFormat.format(MsgTemplate.RECORD_NOT_FOUND, "courseId", courseId));
            return null;
        }
    }

    /**
     * 修改课程封面
     * @param courseId 课程ID
     * @param cover 课程封面
     * @return boolean
     *
     * @author cx
     * @since 2014-12-13
     */
    public boolean updateCover(final long courseId, final String cover) {
        String sql = "UPDATE t_course SET sCover=:cover WHERE id=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("cover", cover)
                .addValue("courseId", courseId);

        log.debug("持久化课程封面修改");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 查询课程列表（授权课程负责人）
     * @param pageNum 页码
     * @param pageSize 每页条数
     * @param specIds 专业ID集合
     * @param keywords 搜索关键字
     * @return Page
     *
     * @author cx
     * @since 2014-12-15
     */
    public Page<Course> getListForSpec(final int pageNum, final int pageSize, List<Long> specIds, String[] keywords) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,iDeptId,sDeptName,iSpecialtyId,sSpecName,iCreatorId,sCreatorName,sRemark FROM t_course");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_course");

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //构建where条件
        StringBuilder where = new StringBuilder(" WHERE 1=1");

        if (!specIds.isEmpty()) {
            log.debug("!specIds.isEmpty()");

            where.append(" AND iSpecialtyId IN (:specIds)");
            sps.addValue("specIds", specIds);
        }

        //拼接搜索关键字
        if (keywords.length > 0) {
            log.debug("keywords.length > 0");
            where.append(" AND (");
            log.debug("循环搜索关键字");
            for (int i = 0; i < keywords.length; i++) {
                where.append("UPPER(sName) LIKE :pattern").append(i).append(" OR ");
                sps.addValue("pattern" + i, "%" + keywords[i].toUpperCase() + "%");
            }
            //去除多余字符
            where.delete(where.length() - 4, where.length());
            where.append(")");
        }

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql.append(where).append(getLimitStr(pageNum, pageSize));

        List<Course> courseList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
                Course entity = new Course();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setDeptId(rs.getLong("iDeptId"));
                entity.setDeptName(rs.getString("sDeptName"));
                entity.setSpecId(rs.getLong("iSpecialtyId"));
                entity.setSpecName(rs.getString("sSpecName"));
                entity.setCreatorId(rs.getLong("iCreatorId"));
                entity.setCreatorName(rs.getString("sCreatorName"));
                entity.setRemark(rs.getString("sRemark"));

                return entity;
            }
        });

        log.debug("查询课程授权分页列表信息");
        return new Page<>(total, pageSize, pageNum, courseList);
    }

    /**
     * 更新课程层级结构名称
     * @param course 课程实体类
     * @return boolean
     *
     * @author cx
     * @since 2014-12-16
     */
    public boolean updateStruct(Course course) {
        String sql = "UPDATE t_course SET sFirtLevel=:firstLevel,sSecondLevel=:secondLevel WHERE id=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("firstLevel", course.getFirstLevel())
                .addValue("secondLevel", course.getSecondLevel())
                .addValue("courseId", course.getId());

        log.debug("持久化层级结构的更新");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 获取课程层级结构
     * @param courseId 课程ID
     * @return Course
     *
     * @author cx
     * @since 2014-12-16
     */
    public Course getStruct(final long courseId) {
        String sql = "SELECT sFirtLevel,sSecondLevel FROM t_course WHERE id=:courseId";
        try {
            log.debug("查询层级结构");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("courseId", courseId),
                    new RowMapper<Course>() {
                        @Override
                        public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
                            Course entity = new Course();

                            entity.setFirstLevel(rs.getString("sFirtLevel"));
                            entity.setSecondLevel(rs.getString("sSecondLevel"));

                            return entity;
                        }
                    });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("未找到指定课程的层级结构");
            log.warn(MessageFormat.format(MsgTemplate.RECORD_NOT_FOUND, "courseId", courseId));
            return null;
        }
    }

    /**
     * 更新课程创建者
     *
     * @author chenxin
     * @since 2014/12/24
     */
    public boolean updateCreator(final long creatorId, final String creatorName, final long courseId) {
        String sql = "UPDATE t_course SET iCreatorId=:creator,sCreatorName=:creatorName WHERE id=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("creator", creatorId)
                .addValue("creatorName", creatorName)
                .addValue("courseId", courseId);

        log.debug("update course creator");

        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 更新课程创建者名称
     * @param creatorId
     * @param creatorName
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-01-06
     */
    public int updateCreatorName(final long creatorId,final String creatorName){
        String sql = "UPDATE t_course SET sCreatorName=:creatorName WHERE iCreatorId=:creatorId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("creatorId", creatorId)
                .addValue("creatorName", creatorName);

        log.debug("update course creatorName");

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 更新课程所属系部名称
     * @param deptId
     * @param deptName
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-01-06
     */
    public int updateDeptName(final long deptId,final String deptName){
        String sql = "UPDATE t_course SET sDeptName=:deptName WHERE iDeptId=:deptId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptId", deptId)
                .addValue("deptName", deptName);

        log.debug("update course departmentName");

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 更新课程所属专业名称
     * @param specialtyId
     * @param specialtyName
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-01-06
     */
    public int updateSpecialtyName(final long specialtyId,final String specialtyName){
        String sql = "UPDATE t_course SET sSpecName=:specialtyName WHERE iSpecialtyId=:specialtyId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("specialtyId", specialtyId)
                .addValue("specialtyName", specialtyName);

        log.debug("update course specialtyName");

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 设置发布
     * @param courseId 课程编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-02-05
     */
    public int setRelease(final long courseId){
        String sql = "UPDATE t_course SET tiStatus=1 WHERE id=:courseId";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("courseId",courseId);

        log.debug("返回发布课程结果");

        return namedParameterJdbcTemplate.update(sql,params);
    }

    /**
     * 获取教师所属的课程列表
     * @param teacherId 教师编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Course> getListForTeacher(Long teacherId){
        String sql = "SELECT id,sName FROM t_course c LEFT JOIN t_teacher_team t ON c.id=t.iCourseId " +
                "WHERE t.iTeacherId=:teacherId";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("teacherId", teacherId), new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();

                course.setId(rs.getLong("id"));
                course.setName(rs.getString("sName"));

                return course;
            }
        });
    }

    /**
     * 根据id集合获取课程集合(已发布)
     * @param courseIds 课程编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Course> getByIds(List<Long> courseIds){
        String sql = "SELECT id,sName FROM t_course WHERE id IN(:courseId)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("courseId", courseIds), new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();

                course.setId(rs.getLong("id"));
                course.setName(rs.getString("sName"));

                return course;
            }
        });
    }

    /**
     * 获取所有已发布课程集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Course> getAll(){
        String sql = "SELECT id,sName FROM t_course";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource(), new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();

                course.setId(rs.getLong("id"));
                course.setName(rs.getString("sName"));

                return course;
            }
        });
    }

    /**
     * 根据课程代码集合获取课程数据集合
     * @param codes 课程代码集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Map<String,Course>> getMapByCodes(List<String> codes) {
        String sql = "SELECT c.id,c.iSpecialtyId,cc.sCode FROM t_course c LEFT JOIN t_course_code cc " +
                "ON c.id=cc.iCourseId WHERE cc.sCode IN(:codes)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("codes", codes), new RowMapper<Map<String, Course>>() {
            @Override
            public Map<String, Course> mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();

                course.setId(rs.getLong("id"));
                course.setSpecId(rs.getLong("iSpecialtyId"));

                Map<String, Course> courseMap = new HashMap<String, Course>();
                courseMap.put(rs.getString("sCode"), course);

                return courseMap;
            }
        });
    }

    /**
     * 根据课程代码集合获取课程集合
     * @param codes 课程代码集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-28
     */
    public List<Course> getListByCodes(final List<String> codes){
        String sql = "SELECT c.id,c.sName FROM t_course c LEFT JOIN t_course_code cc " +
                "ON c.id=cc.iCourseId WHERE cc.sCode IN(:codes)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("codes", codes), new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();

                course.setId(rs.getLong("id"));
                course.setName(rs.getString("sName"));

                return course;
            }
        });
    }

    /**
     * 学生在学课程
     * @param type 课程类型
     * @param csCodeList 课程代码list
     * @param page 页
     * @param pageSize 条数
     * @param courseIdList 用来返回结果集课程ID
     * @return page
     *
     * @author wyh
     * @since 2015-04-02
     */
    public Page<Course> listStudentCourse(int type, List<String> csCodeList, int page, int pageSize, final List<Long> courseIdList){
        StringBuilder sql = new StringBuilder("SELECT c.id,c.sName,c.sEnName,c.sCover,c.dtCreateTime,t.sName sTeacher,t.sPicture,cg.id iGroupId FROM t_course c JOIN t_course_code cc ON c.id=cc.iCourseId ")
                .append("JOIN t_course_group cg ON cc.sCode=cg.sCode LEFT JOIN t_teacher t ON cg.sTeacherNum=t.sNumber WHERE c.tiKind=:type AND cc.sCode IN(:csCodeList) ");
        String sqlTotal = "SELECT COUNT(*) FROM t_course c JOIN t_course_code cc ON c.id=cc.iCourseId " +
                "JOIN t_course_group cg ON cc.sCode=cg.sCode LEFT JOIN t_teacher t ON cg.sTeacherNum=t.sNumber WHERE c.tiKind=:type AND cc.sCode IN(:csCodeList) ";

        if(csCodeList.isEmpty()){
            log.debug("课程代码集合为空：csCodeList.isEmpty()");
            return new Page<>();
        }

        SqlParameterSource sps = new MapSqlParameterSource().addValue("type", type).addValue("csCodeList", csCodeList);
        int total = namedParameterJdbcTemplate.queryForObject(sqlTotal, sps, Integer.class);

        if(total == 0){
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        List<Course> courseList = namedParameterJdbcTemplate.query(sql.append(getLimitStr(page, pageSize)).toString(), sps, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet resultSet, int i) throws SQLException {
                Course course = new Course();
                course.setId(resultSet.getLong("id"));
                course.setName(resultSet.getString("sName"));
                course.setEnName(resultSet.getString("sEnName"));
                course.setCover(resultSet.getString("sCover"));
                course.setCreateTime(resultSet.getTimestamp("dtCreateTime"));
                course.setCourseLeader(resultSet.getString("sTeacher"));
                course.setLeaderPhoto(resultSet.getString("sPicture"));
                course.setGroupId(resultSet.getLong("iGroupId"));
                courseIdList.add(resultSet.getLong("id"));
                return course;
            }
        });

        log.debug("返回 学生在学课程");
        return new Page<>(total, pageSize, page, courseList);
    }


	/**
     * 根据课程代码获取课程ＩＤ、课程名称
     *
     * @param courseCode　课程代码
     * @return　List<Map<String, Object>>
     * @author lyj
     * @since 2015-3-31
     */
    public List<Map<String, Object>> getIdName(final Set<String> courseCode) {
        StringBuilder sql = new StringBuilder("SELECT cou.id as courseId,cou.sName as courseName, cc.sCode as courseCode FROM t_course cou JOIN t_course_code cc ON cou.id = cc.iCourseId " +
                "WHERE cou.id IN (SELECT iCourseId FROM t_course_code WHERE sCode IN (:courseCode))");

        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource("courseCode", courseCode));

    }


    /**
     * 根据课程id获取课程明细
     *
     * @param courseId 课程ID
     * @return Course
     * @author lyj
     * @since 2015-3-31
     */
    public Course getDetail(final long courseId){
        //获取课程中文名称、英文名称、系部名称、专业名称、课程种类、课程性质、课程封面、备注
        StringBuilder sql = new StringBuilder("SELECT sName, sEnName,iDeptId, sDeptName, iSpecialtyId, sSpecName, tiKind, tiNature, sCover, sRemark FROM t_course WHERE id = :id");

        try {
            log.debug("根据课程id获取课程明细");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", courseId), new RowMapper<Course>() {
                @Override
                public Course mapRow(ResultSet resultSet, int i) throws SQLException {
                    Course course = new Course();

                    course.setName(resultSet.getString("sName"));
                    course.setEnName(resultSet.getString("sEnName"));
                    course.setDeptName(resultSet.getString("sDeptName"));
                    course.setSpecName(resultSet.getString("sSpecName"));
                    course.setKind(resultSet.getInt("tiKind"));
                    course.setNature(resultSet.getInt("tiNature"));
                    course.setCover(resultSet.getString("sCover"));
                    course.setRemark(resultSet.getString("sRemark"));
                    course.setSpecId(resultSet.getLong("iSpecialtyId"));
                    course.setDeptId(resultSet.getLong("iDeptId"));

                    return course;
                }

            });
        } catch (EmptyResultDataAccessException ex){
            log.debug("根据课程ID获取课程明细失败");
            log.warn("根据课程ID获取课程明细失败！courseId->{}", courseId);
            return null;
        }
    }


    /**
     * 根据课程代码获取其所属的专业ID集合
     * @param codeList 课程代码集合
     * @return list
     * @author lyj
     * @since 2015-5-6
     */
    public List<Long> getSpecialtiesByCodes(final List codeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT c.iSpecialtyId FROM t_course c JOIN t_course_code cc ON c.id=cc.iCourseId WHERE cc.sCode IN (:codes)");

        log.debug("根据课程代码获取其所属的专业ID集合");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("codes", codeList), Long.class);
    }


    /**
     * 根据专业ID获取课程ID集合
     *
     * @param specId 专业ID
     * @return List<Long>
     * @author lyj
     * @since 2015-5-6
     */
    public List<Course> getIdsBySpecId(final long specId) {
        StringBuilder sql = new StringBuilder("SELECT c.id,cc.sCode FROM t_course c JOIN t_course_code cc ON c.id = cc.iCourseId WHERE iSpecialtyId = :specId");

        log.debug("根据专业ID获取课程ID集合");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("specId", specId), new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet resultSet, int i) throws SQLException {
                Course course = new Course();

                course.setId(resultSet.getLong("id"));
                course.setLessonCode(resultSet.getString("sCode"));

                return course;
            }
        });
    }
}
